-- **************************************************************************
--    Project Name:   集散错扫-明细表
--    Job Name:       jms_dm__dm_report_site_errscan_detail_dt
--    Description :   需要统计集散到件扫描时，实际扫描类型与应扫描类型不一致的件量
--    Author :        马诚
--    date：          2022/7/27
-- **************************************************************************
--    Relation ：
--    jms_dm__dm_report_site_errscan_detail_dt  << [
--     jms_dwd__dwd_tab_barscan_arrival_base_dt,
--      jms_dwd__dwd_tab_barscan_send_base_dt
--      ]
--
--

-- **************************************************************************
--    modify by 马诚 2022/7/27 新增脚本

-- **************************************************************************

with report_site_errscan_send_base_dt as (
        select
         t.scan_time
        ,t.scan_time_ff
        ,t.site_code  --网点编号
        ,t.site_name --网点名称
        ,t.proxy_area_code -- 代理区编号
        ,t.proxy_area_name -- 代理区名称
        ,t.franchisees_code -- 加盟商编号
        ,t.franchisees_name -- 加盟商名称
        ,t.billcode --单号
        ,t.nextstation  -- 下一站名称
        ,t.nextstationcode --下一站编码
        ,t.next_network_type --下一站类型
        ,t.bill_type  --进出港类型
        ,t.should_arrival_scantype  --应扫描类型
        ,t.dt
       from
       (
            select
             t.scan_time
            ,date_format(t.scan_time,'yyyy-MM-dd') as scan_time_ff
            ,t.scan_site_code as  site_code  --网点编号
            ,t.input_site as site_name --网点名称
            ,t.site_agent_code  as proxy_area_code -- 代理区编号
            ,t.site_agent_name  as proxy_area_name -- 代理区名称
            ,t.site_franchisee_code as franchisees_code -- 加盟商编号
            ,t.site_franchisee_name  as franchisees_name -- 加盟商名称
            ,t.waybill_no as billcode --单号
            ,t.next_station as nextstation  -- 下一站名称
            ,t.next_station_code as nextstationcode --下一站编码
            ,t.next_network_type --下一站类型
            ,case when t.next_network_type =2 then 'out_bill' else 'in_bill' end as bill_type  --进出港类型
            ,case when t.next_network_type =2 then '集货到件' else '到件扫描' end as should_arrival_scantype  --应扫描类型
            ,date_format(t.scan_time,'yyyy-MM-dd') as dt
            ,row_number()over(partition by t.waybill_no,t.scan_site_code order by t.scan_time desc ) as rnk
            from
                jms_dwd.dwd_tab_barscan_send_base_dt t
            where
                t.dt >= '{{ execution_date | date_add(-10) | cst_ds }}'
                and t.network_type=3
                and date_format(t.scan_time,'yyyy-MM-dd') = '{{ execution_date | cst_ds }}'
      ) t  where t.rnk=1


),
report_site_errscan_arrival_base_dt as (

    select
          t.waybill_no,
         t.scan_type ,
         t.scan_time ,
         t.scan_site ,
         t.scan_site_code
       from
       (
            select
             t.waybill_no,
             t.scan_type ,
             t.scan_time ,
             t.scan_site ,
             t.scan_site_code ,
             row_number()over(partition by t.waybill_no,t.scan_site_code order by t.scan_time desc ) as rnk
            from
                jms_dwd.dwd_tab_barscan_arrival_base_dt t
            where
                t.dt >= '{{ execution_date | date_add(-10) | cst_ds }}'
                and t.network_type=3

      ) t  where t.rnk=1

)


insert overwrite table jms_dm.dm_report_site_errscan_detail_dt partition (dt)
select
     t.scan_time_ff --扫描日期
    ,t.scan_time  --扫描日期
    ,t.site_code  --网点编号
    ,t.site_name --网点名称
    ,t.proxy_area_code -- 代理区编号
    ,t.proxy_area_name -- 代理区名称
    ,t.franchisees_code -- 加盟商编号
    ,t.franchisees_name -- 加盟商名称
    ,t.billcode --单号
    ,t.nextstation  -- 下一站名称
    ,t.nextstationcode --下一站编码
    ,t.next_network_type --下一站类型
    ,t1.scan_type as real_arrival_scantype  --实际扫描类型
    ,t1.scan_time as arrival_time  --到件时间'
    ,t1.scan_site_code as arrival_scan_site_code --到件网点
    ,t.bill_type  --进出港类型
    ,t.should_arrival_scantype  --应扫描类型
    ,current_timestamp as CREATE_TIME --创建日期
    ,t.dt
from report_site_errscan_send_base_dt t
left join report_site_errscan_arrival_base_dt  t1 on t.billcode=t1.waybill_no and t.site_code=t1.scan_site_code
where
    t1.scan_site_code is not null

 
distribute by t.dt,pmod(hash(t.billcode), 30)
;
